package com.cmcc.zysoft.infocollection.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;
import com.cmcc.zysoft.infocollection.model.Employee;
import com.starit.common.dao.hibernate.HibernateBaseDaoImpl;
/**
 * @author 喻金龙
 * <br />邮箱: yu.jinlong@ustcinfo.com
 * <br />描述: EmployeeDao.java
 * <br />版本: 1.0.0
 * <br />日期: 2013-9-11 下午2:33:01
 * <br />CopyRight © 2012 USTC SINOVATE SOFTWARE CO.LTD All Rights Reserved.
 */
@Repository
public class EmployeeDao extends HibernateBaseDaoImpl<Employee, String> {
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 获取员工列表.
	 * @param page
	 * @param rows
	 * @param keyword 搜索关键字
	 * @param type 类型
	 * @param compOrDpetId 企业或者ID
	 * @return Map<String, Object>
	 */
	public Map<String, Object> getAllEmployee(int page, int rows, String keyword, String type, String compOrDpetId) {
		int offset = (page - 1) * rows;
		Map<String, Object> map = new HashMap<String, Object>();
		String rowSql = 
				" SELECT emp.employee_id AS employeeId, emp.employee_code AS employeeCode, emp.employee_name AS employeeName, emp.mobile, " +
				" d.department_name AS departmentName, emp.email, h.headship_name AS headshipName, c.company_name AS companyName, r.role_name AS roleName " +
				" FROM tb_c_employee emp, tb_c_role r, tb_c_system_user u, tb_c_user_role ur, tb_c_department d, tb_c_headship h, tb_c_company c " +
				" WHERE emp.employee_id = u.employee_id AND u.user_id = ur.user_id AND r.role_id = ur.role_id AND emp.del_flag = '0' " +
				" AND d.department_id = emp.department_id AND h.headship_id = emp.headship_id AND u.company_id = c.company_id ";
		String countSql = 
				" SELECT COUNT(*) " +
				" FROM tb_c_employee emp, tb_c_role r, tb_c_system_user u, tb_c_user_role ur, tb_c_department d, tb_c_headship h, tb_c_company c " +
				" WHERE emp.employee_id = u.employee_id AND u.user_id = ur.user_id AND r.role_id = ur.role_id AND emp.del_flag = '0' " +
				" AND d.department_id = emp.department_id AND h.headship_id = emp.headship_id AND u.company_id = c.company_id ";
		if(StringUtils.hasText(keyword)) {
			rowSql += " AND (emp.employee_name LIKE '%" + keyword + "%' OR emp.mobile LIKE '%" + keyword + "%') ";
			countSql += " AND (emp.employee_name LIKE '%" + keyword + "%' OR emp.mobile LIKE '%" + keyword + "%') ";
		}
		if("company".equals(type)) {
			rowSql += " AND c.company_id = '" + compOrDpetId + "' ";
			countSql += " AND c.company_id = '" + compOrDpetId + "' ";
		} else if("department".equals(type)) {
			rowSql += " AND d.department_id = '" + compOrDpetId + "' ";
			countSql += " AND d.department_id = '" + compOrDpetId + "' ";
		}
		rowSql += " LIMIT ?, ? ";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(rowSql, offset, rows);
		int total = this.jdbcTemplate.queryForInt(countSql);
		map.put("rows", list);
		map.put("total", total);
		return map;
	}
	
	/**
	 * 获取员工列表.
	 * @param page
	 * @param rows
	 * @param keyword 搜索关键字
	 * @return Map<String, Object>
	 */
	public Map<String, Object> getAllEmployee(int page, int rows, String keyword) {
		int offset = (page - 1) * rows;
		Map<String, Object> map = new HashMap<String, Object>();
		String rowSql = 
				" SELECT emp.employee_id AS employeeId, emp.employee_code AS employeeCode, emp.employee_name AS employeeName, emp.mobile, " +
				" d.department_name AS departmentName, emp.email, h.headship_name AS headshipName, c.company_name AS companyName, r.role_name AS roleName " +
				" FROM tb_c_employee emp, tb_c_role r, tb_c_system_user u, tb_c_user_role ur, tb_c_department d, tb_c_headship h, tb_c_company c " +
				" WHERE emp.employee_id = u.employee_id AND u.user_id = ur.user_id AND r.role_id = ur.role_id AND emp.del_flag = '0' " +
				" AND d.department_id = emp.department_id AND h.headship_id = emp.headship_id AND u.company_id = c.company_id ";
		String countSql = 
				" SELECT COUNT(*) " +
				" FROM tb_c_employee emp, tb_c_role r, tb_c_system_user u, tb_c_user_role ur, tb_c_department d, tb_c_headship h, tb_c_company c " +
				" WHERE emp.employee_id = u.employee_id AND u.user_id = ur.user_id AND r.role_id = ur.role_id AND emp.del_flag = '0' " +
				" AND d.department_id = emp.department_id AND h.headship_id = emp.headship_id AND u.company_id = c.company_id ";
		if(StringUtils.hasText(keyword)) {
			rowSql += " AND (emp.employee_name LIKE '%" + keyword + "%' OR emp.mobile LIKE '%" + keyword + "%') ";
			countSql += " AND (emp.employee_name LIKE '%" + keyword + "%' OR emp.mobile LIKE '%" + keyword + "%') ";
		}
		rowSql += " LIMIT ?, ? ";
		List<Map<String, Object>> list = this.jdbcTemplate.queryForList(rowSql, offset, rows);
		int total = this.jdbcTemplate.queryForInt(countSql);
		map.put("rows", list);
		map.put("total", total);
		return map;
	}
	
	/**
	 * 部门类型下拉框
	 * @param 
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> departmentTree() {
		String hql = " select new Map(d.departmentId as id,d.departmentName as text) " +
				" from Department d where d.delFlag='0'" ;
		return this.findByHQL(hql);
	}
	
	/**
	 * 根据企业ID获取其岗位列表.
	 * @param companyId 企业ID
	 * @return List<Map<String, Object>>
	 */
	public List<Map<String, Object>> headships(String companyId) {
		String sql = 
				" SELECT a.headship_id AS headshipId, a.headship_name AS headshipName, a.company_id AS companyId, a.* " +
				" FROM tb_c_headship a " +
				" WHERE a.del_flag = '0' AND a.status = '0' AND a.company_id = ? ";
		return this.jdbcTemplate.queryForList(sql, companyId);
	}
	
	/**
	 * 获取角色列表.
	 * @return List<Map<String,Object>>
	 */
	public List<Map<String, Object>> roles() {
		String sql = " SELECT a.role_id AS roleId, a.role_name AS roleName, a.* FROM tb_c_role a " +
				" WHERE a.status = '0' ORDER BY a.display_order ";
		return this.jdbcTemplate.queryForList(sql);
	}
	
	/**
	 * 根据员工id获取员工信息
	 * 
	 * @param employeeId 员工id
	 * @return
	 */
	public Employee getEmployee(String employeeId) {
		Employee employee = this.get(employeeId);
		return employee;
	}
	
	
	
	/**
	 * 判断员工编号是否同名
	 * @param employeeCode employeeId
	 * @return true表示同名   false表示不同名
	 */
	public boolean isEmployeeCodeEqual(String employeeCode,String employeeId){
		String checkSql = "select e.* from tb_c_employee e where e.del_flag ='0' and e.employee_code = '"+ employeeCode +"'";
		if("0".equals(employeeId)==false){
			checkSql += " AND e.employee_id != '" + employeeId + "'";
		}
		List<Map<String, Object>> list=null;
		list=this.jdbcTemplate.queryForList(checkSql);
		int listSize=list.size();
		if(listSize==0){return false;}
		else{ return true;}		
	}
	
	/**
	 * 修改员工信息
	 * 
	 * @param employee 员工对象
	 */
	public void updateEmployee(Employee employee) {
		this.getHibernateTemplate().merge(employee);
	}
	
	/**
	 * 判断手机号码是否存在
	 * @param 
	 * @return true表示同名   false表示不同名
	 */
	public boolean isPhoneNumexist(String phoneNumber , String employeeId){
		String checkSql = "select e.* from tb_c_employee e where e.del_flag='0' and e.mobile = '"+ phoneNumber +"'";
		if("0".equals(employeeId)==false){
			checkSql += " AND e.employee_id != '" + employeeId + "'";
		}
		List<Map<String, Object>> list=null;
		list=this.jdbcTemplate.queryForList(checkSql);
		int listSize=list.size();
		if(listSize==0){return false;}
		else{ return true;}		
	}
	
	/**
	 * 物理删除员工信息
	 * 
	 * @param employeeId
	 */
	public void deleteEmployee(String employeeId) {
		String sql = " delete from tb_c_employee where employee_id = '" + employeeId + "'";
		jdbcTemplate.execute(sql);
	}
	
	/**
	 * 删除员工时 自动物理删除  用户表里的员工
	 * @param employeeId
	 */
	public void deleteSystemUser(String employeeId) {
		String sql = " delete from tb_c_system_user where employee_id = '" + employeeId + "'";
		jdbcTemplate.execute(sql);
	}
	
	/**
	 * 删除员工时 自动物理删除  用户角色表里的相关信息
	 * @param userId
	 */
	public void deleteUserRole(String userId) {
		String sql = " delete from tb_c_user_role where user_id = '" + userId + "'";
		jdbcTemplate.execute(sql);
	}
}
